﻿CREATE PROCEDURE [dbo].[usp_select_Reports_ResubmissionsByService]
(
	@Application		varchar(255)	= null,
	@ServiceName		varchar(255)	= null,
   @StartDate			datetime      = '2000/1/1'   
   ,@EndDate			datetime      = '2010/12/31'   
   ,@Debug              bit              = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10)
DECLARE @ErrorCode         int

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: '


SELECT
	 ActionName
	,al.MessageID
	,al.NativeMessageID
	,al.FaultID
	,al.NativeFaultID
	,al.ContentType
	,alm.MessageData
	,al.ResubmitURL
	,al.ResubmitCode
	,al.ResubmitMessage
	,al.AuditDate
	,al.AuditUserName
	,al.Application
from /*
	Fault F 
	INNER JOIN
	AuditLog al ON F.FaultID = AL.FaultID 
		INNER JOIN ActionType at ON al.ActionTypeID = at.ActionTypeID

			LEFT JOIN AuditLogMessageData alm ON al.AuditLogID = alm.AuditLogID
*/
   Fault F 
INNER JOIN
                      AuditLog AL ON F.FaultID = AL.FaultID INNER JOIN 
  ActionType at ON al.ActionTypeID = AT.ActionTypeID

 LEFT OUTER JOIN
                      AuditLogMessageData ALM ON AL.AuditLogID = ALM.AuditLogID

WHERE	AuditDate BETWEEN @StartDate and @EndDate
and al.[application] = @Application
and F.[servicename] = @servicename

AND  ActionName='SuccessfulResubmit'
SET NOCOUNT OFF;
